package com.htdata.iiot.api.tsdb.utils;

import com.htdata.iiot.api.tsdb.config.Configs;
import com.htdata.iiot.api.tsdb.pojo.DeviceNumber;
import com.htiiot.common.db.DbConnectionPool;
import com.htiiot.common.db.JDBCUtils;
import org.apache.hadoop.hbase.client.ConnectionFactory;
import org.apache.log4j.Logger;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBQuery {
    private static Logger log = Logger.getLogger(DBQuery.class);

    /**
     * 根据租户id查询该租户下所有metric(tsdb)
     * @param tenantIds 租户id(若有多个以逗号隔开),如果为null则结果为所有租户的metric(tsdb)
     * @return
     */
    public List<String> metircQuery(String tenantIds){
        DbConnectionPool pool = DbConnectionPool.getPool();
        String sql1 = " select * from (SELECT  T2.* FROM" + "  (SELECT" + "    T.*," + "    CASE" + "      WHEN @mid = struct_id"
                + "      THEN @row := @row + 1" + "      ELSE @row := 1" + "    END rownum,"
                + "    @mid := struct_id MID" + "  FROM" + "    (SELECT" + "      struct_id," + "      id,tenant_id,"
                + "      SUM(1)" + "    FROM" + "      thing_device" + "      WHERE"
                + "        deleted=0 AND TYPE=0" + "    GROUP BY struct_id,id,tenant_id"
                + "    ORDER BY struct_id,id," + "      SUM(1) DESC) AS T) AS T2 ) t " ;
        if(tenantIds!=null&&!tenantIds.trim().isEmpty()){
            sql1+=" where `tenant_id` in("+tenantIds+") ";
        }
        Connection conn = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSet executeQuery = null;
        PreparedStatement prepareStatement = null;
        List<String> metricL = new ArrayList<String>();
        try {
            ps = conn.prepareStatement(sql1);
            rs = ps.executeQuery();
            while (rs.next()) {
                int deviceId = rs.getInt("id");
                int tenant_id = rs.getInt("tenant_id");
                // 2.根据设备获取测点
                String sql2 = "SELECT" + "  t1.id AS id," + "  t2.`name` AS name,t2.min AS valueMin,t2.max AS valueMax,"
                        + "  t3.`name` AS componentname,"
                        + "  t4.`name` AS devicename" + " FROM" + "  `thing_device_component_rel` t1,"
                        + "  `thing_data_stream` t2," + "  thing_component t3," + "  `thing_device` t4"
                        + " WHERE t1.`component_id` = t3.`id`" + "  AND t3.deleted = 0"
                        + "  AND t2.`component_id` = t3.`id`" + "  AND t1.deleted = 0" + "  AND t1.device_id = ?"
                        + "  AND t2.deleted = 0" + "  AND t2.tenant_id = ?" + "  AND t4.id = t1.`device_id`";
                prepareStatement = conn.prepareStatement(sql2);
                prepareStatement.setInt(1, deviceId);
                prepareStatement.setInt(2, tenant_id);
                executeQuery = prepareStatement.executeQuery();
                while (executeQuery.next()) {
                    String name = executeQuery.getString("name");
                    long mId = executeQuery.getLong("id");
//                    System.out.println(new DeviceNumber(tenant_id, deviceId, mId).toHexString()+"_"+name);
                    metricL.add(new DeviceNumber(tenant_id, deviceId, mId).toHexString()+"_"+name);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            log.error(e);
        } finally {
            JDBCUtils.close(ps, rs, conn);
            JDBCUtils.close(prepareStatement, executeQuery, null);
        }
        return metricL;
    }

    /**
     * 根据租户id查询该租户下所有metric(tsdb)
     * @param tenantIds 租户id(若有多个以逗号隔开),如果为null则结果为所有租户的metric(tsdb)
     * @return string[0]=>dn string[1]=>tid string[2]=>did
     */
    public List<String[]> metircQueryWithTidDid(String tenantIds){
        DbConnectionPool pool = DbConnectionPool.getPool();
        String sql1 = " select * from (SELECT  T2.* FROM" + "  (SELECT" + "    T.*," + "    CASE" + "      WHEN @mid = struct_id"
                + "      THEN @row := @row + 1" + "      ELSE @row := 1" + "    END rownum,"
                + "    @mid := struct_id MID" + "  FROM" + "    (SELECT" + "      struct_id," + "      id,tenant_id,"
                + "      SUM(1)" + "    FROM" + "      thing_device" + "      WHERE"
                + "        deleted=0 AND TYPE=0" + "    GROUP BY struct_id,id,tenant_id"
                + "    ORDER BY struct_id,id," + "      SUM(1) DESC) AS T) AS T2 ) t " ;
        if(tenantIds!=null&&!tenantIds.trim().isEmpty()){
            sql1+=" where `tenant_id` in("+tenantIds+") ";
        }
        Connection conn = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSet executeQuery = null;
        PreparedStatement prepareStatement = null;
        List<String[]> metricL = new ArrayList<String[]>();
        try {
            ps = conn.prepareStatement(sql1);
            rs = ps.executeQuery();
            while (rs.next()) {
                int deviceId = rs.getInt("id");
                int tenant_id = rs.getInt("tenant_id");
                // 2.根据设备获取测点
                String sql2 = "SELECT" + "  t1.id AS id," + "  t2.`name` AS name,t2.min AS valueMin,t2.max AS valueMax,"
                        + "  t3.`name` AS componentname,"
                        + "  t4.`name` AS devicename" + " FROM" + "  `thing_device_component_rel` t1,"
                        + "  `thing_data_stream` t2," + "  thing_component t3," + "  `thing_device` t4"
                        + " WHERE t1.`component_id` = t3.`id`" + "  AND t3.deleted = 0"
                        + "  AND t2.`component_id` = t3.`id`" + "  AND t1.deleted = 0" + "  AND t1.device_id = ?"
                        + "  AND t2.deleted = 0" + "  AND t2.tenant_id = ?" + "  AND t4.id = t1.`device_id`";
                prepareStatement = conn.prepareStatement(sql2);
                prepareStatement.setInt(1, deviceId);
                prepareStatement.setInt(2, tenant_id);
                executeQuery = prepareStatement.executeQuery();
                while (executeQuery.next()) {
                    String name = executeQuery.getString("name");
                    long mId = executeQuery.getLong("id");
//                    System.out.println(new DeviceNumber(tenant_id, deviceId, mId).toHexString()+"_"+name);
                    String[] r = new String[3];
                    r[0] = Configs.TSDBConfig.WITH_METRIC==true?new DeviceNumber(tenant_id, deviceId, mId).toHexString()+"_"+name:new DeviceNumber(tenant_id, deviceId, mId).toHexString();
                    r[1] = String.valueOf(tenant_id);
                    r[2] = String.valueOf(deviceId);
                    metricL.add(r);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            log.error(e);
        } finally {
            JDBCUtils.close(ps, rs, conn);
            JDBCUtils.close(prepareStatement, executeQuery, null);
        }
        return metricL;
    }

    /**
     * 通过metricId获取dn
     * @param metricIds 多个metricId以逗号隔开
     * @return string[0]=>dn string[1]=>tid string[2]=>did
     */
    public List<String[]> getDnByMetricId(String metricIds){
        DbConnectionPool pool = DbConnectionPool.getPool();
        String sql = "select t1.tenant_id,t2.device_id,t2.id,t1.name \n" +
                "from thing_data_stream t1,thing_device_component_rel t2\n" +
                "where t1.component_id=t2.component_id and t2.id in ("+metricIds+")";
        Connection conn = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<String[]> dnList = new ArrayList<>();
        try{
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                int tid = rs.getInt("tenant_id");
                int did = rs.getInt("device_id");
                long mid = rs.getLong("id");
                String name = rs.getString("name");
                String dn = Configs.TSDBConfig.WITH_METRIC==true?new DeviceNumber(tid, did, mid).toHexString()+"_"+name:new DeviceNumber(tid, did, mid).toHexString();
                String[] stringArr = new String[3];
                stringArr[0] = dn;
                stringArr[1] = String.valueOf(tid);
                stringArr[2] = String.valueOf(did);
                dnList.add(stringArr);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            log.error(e);
        } finally {
            JDBCUtils.close(ps, rs, conn);
        }
        return dnList;
    }
}
